package com.jt.www.demo;

import com.alibaba.fastjson.JSON;
import com.google.common.collect.Maps;
import com.jt.www.domain.dto.WxbDetailDto;
import com.jt.www.domain.dto.WxbDto;
import net.sf.jxls.transformer.XLSTransformer;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.assertj.core.util.Lists;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.List;
import java.util.Map;

/**
 * Created by shaoyn on 2019/09/18.
 */
public class Demo {
    private static final String a = "[{\"branchCode\":\"JT0137015\",\"branchName\":\"东营分公司\",\"commissionMonth\":0,\"commissionMonthPlan\":0,\"commissionMonthPlanCompletion\":9999,\"commissionYear\":317.9600,\"commissionYearMonthPlan\":0,\"commissionYearMonthPlanCompletion\":9999,\"commissionYearPlan\":0,\"commissionYearPlanCompletion\":9999,\"premLastYear\":200.0000,\"wxbDetailDtos\":[{\"bizType2\":\"201\",\"bizType2Name\":\"阳光财产安责保护伞\",\"branchCode\":\"JT0137015\",\"commissionMonth\":0,\"commissionYear\":0,\"premMonth\":0,\"premMonthPast\":0,\"premPast\":0,\"premToday\":0,\"premYear\":0},{\"bizType2\":\"202\",\"bizType2Name\":\"华泰财险安康保护伞\",\"branchCode\":\"JT0137015\",\"commissionMonth\":0,\"commissionYear\":301.9200,\"premMonth\":0,\"premMonthPast\":0,\"premPast\":800.0000,\"premToday\":0,\"premYear\":800.0000},{\"bizType2\":\"203\",\"bizType2Name\":\"泰然无忧-百万医疗险\",\"branchCode\":\"JT0137015\",\"commissionMonth\":0,\"commissionYear\":0,\"premMonth\":0,\"premMonthPast\":0,\"premPast\":0,\"premToday\":0,\"premYear\":0},{\"bizType2\":\"204\",\"bizType2Name\":\"华泰财险成长护航重疾险\",\"branchCode\":\"JT0137015\",\"commissionMonth\":0,\"commissionYear\":0,\"premMonth\":0,\"premMonthPast\":0,\"premPast\":0,\"premToday\":0,\"premYear\":0},{\"bizType2\":\"205\",\"bizType2Name\":\"监护无忧加倍保\",\"branchCode\":\"JT0137015\",\"commissionMonth\":0,\"commissionYear\":0,\"premMonth\":0,\"premMonthPast\":0,\"premPast\":0,\"premToday\":0,\"premYear\":0},{\"bizType2\":\"206\",\"bizType2Name\":\"关爱保\",\"branchCode\":\"JT0137015\",\"commissionMonth\":0,\"commissionYear\":16.0400,\"premMonth\":0,\"premMonthPast\":0,\"premPast\":170.0000,\"premToday\":0,\"premYear\":170.0000}]},{\"branchCode\":\"JT0134001\",\"branchName\":\"合肥分公司\",\"commissionMonth\":0,\"commissionMonthPlan\":0,\"commissionMonthPlanCompletion\":9999,\"commissionYear\":25268.1400,\"commissionYearMonthPlan\":0,\"commissionYearMonthPlanCompletion\":9999,\"commissionYearPlan\":0,\"commissionYearPlanCompletion\":9999,\"premLastYear\":88886.1200,\"wxbDetailDtos\":[{\"bizType2\":\"201\",\"bizType2Name\":\"阳光财产安责保护伞\",\"branchCode\":\"JT0134001\",\"commissionMonth\":0,\"commissionYear\":0,\"premMonth\":0,\"premMonthPast\":0,\"premPast\":0,\"premToday\":0,\"premYear\":0},{\"bizType2\":\"202\",\"bizType2Name\":\"华泰财险安康保护伞\",\"branchCode\":\"JT0134001\",\"commissionMonth\":0,\"commissionYear\":0,\"premMonth\":0,\"premMonthPast\":0,\"premPast\":0,\"premToday\":0,\"premYear\":0},{\"bizType2\":\"203\",\"bizType2Name\":\"泰然无忧-百万医疗险\",\"branchCode\":\"JT0134001\",\"commissionMonth\":0,\"commissionYear\":0,\"premMonth\":0,\"premMonthPast\":0,\"premPast\":0,\"premToday\":0,\"premYear\":0},{\"bizType2\":\"204\",\"bizType2Name\":\"华泰财险成长护航重疾险\",\"branchCode\":\"JT0134001\",\"commissionMonth\":0,\"commissionYear\":0,\"premMonth\":0,\"premMonthPast\":0,\"premPast\":0,\"premToday\":0,\"premYear\":0},{\"bizType2\":\"205\",\"bizType2Name\":\"监护无忧加倍保\",\"branchCode\":\"JT0134001\",\"commissionMonth\":0,\"commissionYear\":0,\"premMonth\":0,\"premMonthPast\":0,\"premPast\":0,\"premToday\":0,\"premYear\":0},{\"bizType2\":\"206\",\"bizType2Name\":\"关爱保\",\"branchCode\":\"JT0134001\",\"commissionMonth\":0,\"commissionYear\":0,\"premMonth\":0,\"premMonthPast\":0,\"premPast\":0,\"premToday\":0,\"premYear\":0}]},{\"branchCode\":\"JT0137013\",\"branchName\":\"威海分公司\",\"commissionMonth\":0,\"commissionMonthPlan\":0,\"commissionMonthPlanCompletion\":9999,\"commissionYear\":591926.1600,\"commissionYearMonthPlan\":0,\"commissionYearMonthPlanCompletion\":9999,\"commissionYearPlan\":0,\"commissionYearPlanCompletion\":9999,\"premLastYear\":4615961.0000,\"wxbDetailDtos\":[{\"bizType2\":\"201\",\"bizType2Name\":\"阳光财产安责保护伞\",\"branchCode\":\"JT0137013\",\"commissionMonth\":0,\"commissionYear\":0,\"premMonth\":0,\"premMonthPast\":0,\"premPast\":0,\"premToday\":0,\"premYear\":0},{\"bizType2\":\"202\",\"bizType2Name\":\"华泰财险安康保护伞\",\"branchCode\":\"JT0137013\",\"commissionMonth\":0,\"commissionYear\":0,\"premMonth\":0,\"premMonthPast\":0,\"premPast\":0,\"premToday\":0,\"premYear\":0},{\"bizType2\":\"203\",\"bizType2Name\":\"泰然无忧-百万医疗险\",\"branchCode\":\"JT0137013\",\"commissionMonth\":0,\"commissionYear\":0,\"premMonth\":0,\"premMonthPast\":0,\"premPast\":0,\"premToday\":0,\"premYear\":0},{\"bizType2\":\"204\",\"bizType2Name\":\"华泰财险成长护航重疾险\",\"branchCode\":\"JT0137013\",\"commissionMonth\":0,\"commissionYear\":0,\"premMonth\":0,\"premMonthPast\":0,\"premPast\":0,\"premToday\":0,\"premYear\":0},{\"bizType2\":\"205\",\"bizType2Name\":\"监护无忧加倍保\",\"branchCode\":\"JT0137013\",\"commissionMonth\":0,\"commissionYear\":0,\"premMonth\":0,\"premMonthPast\":0,\"premPast\":0,\"premToday\":0,\"premYear\":0},{\"bizType2\":\"206\",\"bizType2Name\":\"关爱保\",\"branchCode\":\"JT0137013\",\"commissionMonth\":0,\"commissionYear\":0,\"premMonth\":0,\"premMonthPast\":0,\"premPast\":0,\"premToday\":0,\"premYear\":0}]},{\"branchCode\":\"JT0132003\",\"branchName\":\"苏州分公司\",\"commissionMonth\":0,\"commissionMonthPlan\":0,\"commissionMonthPlanCompletion\":9999,\"commissionYear\":1878.6600,\"commissionYearMonthPlan\":0,\"commissionYearMonthPlanCompletion\":9999,\"commissionYearPlan\":0,\"commissionYearPlanCompletion\":9999,\"premLastYear\":11565.2300,\"wxbDetailDtos\":[{\"bizType2\":\"201\",\"bizType2Name\":\"阳光财产安责保护伞\",\"branchCode\":\"JT0132003\",\"commissionMonth\":0,\"commissionYear\":0,\"premMonth\":0,\"premMonthPast\":0,\"premPast\":0,\"premToday\":0,\"premYear\":0},{\"bizType2\":\"202\",\"bizType2Name\":\"华泰财险安康保护伞\",\"branchCode\":\"JT0132003\",\"commissionMonth\":0,\"commissionYear\":0,\"premMonth\":0,\"premMonthPast\":0,\"premPast\":0,\"premToday\":0,\"premYear\":0},{\"bizType2\":\"203\",\"bizType2Name\":\"泰然无忧-百万医疗险\",\"branchCode\":\"JT0132003\",\"commissionMonth\":0,\"commissionYear\":0,\"premMonth\":0,\"premMonthPast\":0,\"premPast\":0,\"premToday\":0,\"premYear\":0},{\"bizType2\":\"204\",\"bizType2Name\":\"华泰财险成长护航重疾险\",\"branchCode\":\"JT0132003\",\"commissionMonth\":0,\"commissionYear\":0,\"premMonth\":0,\"premMonthPast\":0,\"premPast\":0,\"premToday\":0,\"premYear\":0},{\"bizType2\":\"205\",\"bizType2Name\":\"监护无忧加倍保\",\"branchCode\":\"JT0132003\",\"commissionMonth\":0,\"commissionYear\":0,\"premMonth\":0,\"premMonthPast\":0,\"premPast\":0,\"premToday\":0,\"premYear\":0},{\"bizType2\":\"206\",\"bizType2Name\":\"关爱保\",\"branchCode\":\"JT0132003\",\"commissionMonth\":0,\"commissionYear\":0,\"premMonth\":0,\"premMonthPast\":0,\"premPast\":0,\"premToday\":0,\"premYear\":0}]},{\"branchCode\":\"JT0144002\",\"branchName\":\"深圳分公司\",\"commissionMonth\":0,\"commissionMonthPlan\":0.00,\"commissionMonthPlanCompletion\":9999,\"commissionYear\":80299.3200,\"commissionYearMonthPlan\":0.00,\"commissionYearMonthPlanCompletion\":9999,\"commissionYearPlan\":0.00,\"commissionYearPlanCompletion\":9999,\"premLastYear\":1016524.9000,\"wxbDetailDtos\":[{\"bizType2\":\"201\",\"bizType2Name\":\"阳光财产安责保护伞\",\"branchCode\":\"JT0144002\",\"commissionMonth\":0,\"commissionYear\":0,\"premMonth\":0,\"premMonthPast\":0,\"premPast\":0,\"premToday\":0,\"premYear\":0},{\"bizType2\":\"202\",\"bizType2Name\":\"华泰财险安康保护伞\",\"branchCode\":\"JT0144002\",\"commissionMonth\":0,\"commissionYear\":0,\"premMonth\":0,\"premMonthPast\":0,\"premPast\":0,\"premToday\":0,\"premYear\":0},{\"bizType2\":\"203\",\"bizType2Name\":\"泰然无忧-百万医疗险\",\"branchCode\":\"JT0144002\",\"commissionMonth\":0,\"commissionYear\":0,\"premMonth\":0,\"premMonthPast\":0,\"premPast\":0,\"premToday\":0,\"premYear\":0},{\"bizType2\":\"204\",\"bizType2Name\":\"华泰财险成长护航重疾险\",\"branchCode\":\"JT0144002\",\"commissionMonth\":0,\"commissionYear\":0,\"premMonth\":0,\"premMonthPast\":0,\"premPast\":0,\"premToday\":0,\"premYear\":0},{\"bizType2\":\"205\",\"bizType2Name\":\"监护无忧加倍保\",\"branchCode\":\"JT0144002\",\"commissionMonth\":0,\"commissionYear\":0,\"premMonth\":0,\"premMonthPast\":0,\"premPast\":0,\"premToday\":0,\"premYear\":0},{\"bizType2\":\"206\",\"bizType2Name\":\"关爱保\",\"branchCode\":\"JT0144002\",\"commissionMonth\":0,\"commissionYear\":0,\"premMonth\":0,\"premMonthPast\":0,\"premPast\":0,\"premToday\":0,\"premYear\":0}]}]";

    public static void main(String[] args) {

        List<WxbDto> wxbDto = JSON.parseArray(a, WxbDto.class);
        List<String> projects = Lists.newArrayList();
        for (WxbDetailDto detail : wxbDto.get(0).getWxbDetailDtos()) {
            projects.add(detail.getBizType2Name());
        }
        create(wxbDto, projects);
    }

    public static void create(List<WxbDto> wxbDto, List<String> projects) {
        //解析结果
        try {
            Map<String, Object> beanParams = Maps.newHashMap();
            beanParams.put("list", wxbDto);
            beanParams.put("projects", projects);
            beanParams.put("lastYear", 2019);
            beanParams.put("year", 2020);
            beanParams.put("month", 11);


            XLSTransformer transformer = new XLSTransformer();
            transformer.setJexlInnerCollectionsAccess(true);

            File file = new File("C:\\Users\\terminal\\Desktop\\1.xlsx");

            Workbook workBook = transformer.transformXLS(new FileInputStream(file), beanParams);
//
            Sheet sheet = workBook.getSheetAt(0);// 1 1
            //合并当年全年已确认保费
            sheet.addMergedRegion(new CellRangeAddress(1, 1, 3, 10 + projects.size() * 7));
            //合并第一行
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 10 + projects.size() * 7));
            //合并项目
            for (int i = 0; i < projects.size(); i++) {
                sheet.addMergedRegion(new CellRangeAddress(2, 3, 3 + i * 4, 6 + i * 4));
            }
            //本年当月情况
            sheet.addMergedRegion(new CellRangeAddress(2, 2, 3 + projects.size() * 4, 5 + projects.size() * 7));
            for (int i = 0; i < projects.size(); i++) {
                sheet.addMergedRegion(new CellRangeAddress(3, 3, 3 + projects.size() * 4 + i * 3, 5 + projects.size() * 4 + i * 3));
            }
            outFile(workBook, "C:\\Users\\terminal\\Desktop\\2.xlsx");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void outFile(Workbook workbook, String path) {
        try {
            OutputStream os = new FileOutputStream(new File(path));
            workbook.write(os);
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}

